﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace PhanMemDao
{
    class DataAccess
    {
        public SqlConnection sqlCon;
        public SqlCommand sqlCom;
        public SqlDataAdapter sqlAdap;
        SqlTransaction objTransaction = null;
        //Tao va mo ket noi
        public void ketnoi()
        {
            string str = @"Data Source=TUANANH-PC\SQLEXPRESS;Initial Catalog=PhanMemHocC;Integrated Security=True";//Bo duogn link den dia chi co so su lieu cua cac cau vao day nhe!
            sqlCon = new SqlConnection(str);
            sqlCon.Open();
        }
        //Dong ket noi toi co so du lieu
        public void dongketnoi()
        {
            if (sqlCon.State != ConnectionState.Closed)
            {
                sqlCon.Close();
            }
        }
        //Phuong thuc lay du lieu
        //@sql: cau lenh truy van selectt phuc vu cho tim kiem
        public DataTable lay_du_lieu(string sql)
        {
            ketnoi();
            DataTable dt = new DataTable();
            SqlCommand com = new SqlCommand();
            com.Connection = sqlCon;
            com.CommandType = CommandType.Text;
            com.CommandText = sql;
            try
            {
                SqlDataAdapter dA = new SqlDataAdapter(com);
                dA.SelectCommand.Transaction = objTransaction;
                dA.Fill(dt);

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                com.Dispose();
                dongketnoi();
            }
            return dt;
        }
        //phuong thuc dung de cap nhat du lieu
        //@sql: chuoi cau lenh truy van den co so du lieu: insert,delete,update
        public void thao_tac_du_lieu(string sql)
        {
            ketnoi();
            SqlCommand com = new SqlCommand();
            SqlTransaction tra = null;
            com.Connection= sqlCon;
            com.CommandType = CommandType.Text;
            com.CommandText = sql;
            try
            {
                tra = sqlCon.BeginTransaction();
                com.Transaction = tra;
                com.ExecuteNonQuery();
                tra.Commit();
            }
            catch (Exception exp)
            {
                tra.Rollback();
                Console.WriteLine(exp.Message);
            }
            finally
            {
                com.Dispose();
                dongketnoi();
            }
        }
    }
}
